﻿
Partial Class WebForm_ManageTCUploadExcel_ManageTCUploadExcelByCG
    Inherits System.Web.UI.UserControl

#Region "事件"
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            Me.BindDDLUploadType()
            Me.BindDDLDataType()
            Me.BindVersion()
            Me.BindDDLDivision()
        End If
    End Sub

    Protected Sub btnUpload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpload.Click

        If Not FileUpload1.HasFile Then
            MyObj.ShowMessage("no file")
            Return
        ElseIf IO.Path.GetExtension(FileUpload1.PostedFile.FileName).ToUpper() <> ".XLS" AndAlso IO.Path.GetExtension(FileUpload1.PostedFile.FileName).ToUpper() <> ".XLSX" Then
            MyObj.ShowMessage("Import failed!!Please select the file extension (xls/xlsx) file!!")
            Return
        End If

        '檢查資料表是否存在
        Dim TableName As String = Me.HeaderTB & Me.ddlVersion.SelectedValue
        Dim dat As New clsData
        Dim sql As New StringBuilder
        sql.AppendFormat("Select * from sysobjects where name='{0}'", TableName)
        If Not dat.GetData(sql.ToString()).Rows.Count > 0 Then
            MyObj.ShowMessage("Table missed.")
            Return
        End If

        Dim AuthDT As DataTable
        'Sig權限
        Dim stManager As New SignatureManager
        Dim SigList As New List(Of String)
        SigList = stManager.GetSignatures(Me.ddlDivision.SelectedValue)

        'Channel權限
        Dim cmManager As New ChannelManager
        Dim ChannelList As New List(Of String)
        ChannelList = cmManager.GetAllChannelIDs(Me.ddlDivision.SelectedValue, "")

        'SecondLevel
        Dim lManager As New LevelManager
        Dim LevelName As String = ""
        Dim CodeString As String = "''"
        Dim LevelRow As DataRow = lManager.GetLevelName2(Me.ddlDivision.SelectedValue, Me.HeaderTB & "TC", 2)
        If LevelRow IsNot Nothing Then
            LevelName = LevelRow("LevelName")
            If LevelRow("Chain") = "Y" Then
                CodeString = String.Format("substring(B.Code,1,{0}*2)", LevelRow("Sequence"))
            Else
                CodeString = String.Format("substring(B.Code,{0}*2-1,2)", LevelRow("Sequence"))
            End If
        End If

        '取出有權限的Signature、SecondLevel、Channel
        sql.Length = 0
        sql.AppendFormat("Select Distinct A.Code as Signature ")
        'sql.AppendFormat(",A.Description as SignatureText ")
        sql.AppendFormat(",{0} as SecondLevel ", CodeString)
        'sql.AppendFormat(",B.Description as SecondLevelText ")
        sql.AppendFormat(",C.Channel as ChannelID ")
        'sql.AppendFormat(",C.ChannelName ")
        sql.AppendFormat("from ProductHierarchyOriginal A ")
        sql.AppendFormat("Left join ProductHierarchyOriginal B ")
        sql.AppendFormat("	On A.Code=LEFT(B.Code,2) and B.LevelName='{0}' ", LevelName)
        sql.AppendFormat("Left join ChannelView C  ")
        sql.AppendFormat("	on A.Code=C.Signature ")
        If ChannelList.Count = 0 Then
            sql.AppendFormat("  And 1=2 ")
        Else
            sql.AppendFormat("  And C.Channel IN ({0}) ", MyObj.JoinFormat(",", "'{0}'", ChannelList))
        End If
        sql.AppendFormat("where A.LevelName='Signature' ")
        If SigList.Count = 0 Then
            sql.AppendFormat("And 1=2 ")
        Else
            sql.AppendFormat("And A.Code IN ({0}) ", MyObj.JoinFormat(",", "'{0}'", SigList))
        End If
        sql.AppendFormat("And A.Code Not in ")
        sql.AppendFormat("( ")
        sql.AppendFormat("  Select Signature from VersionControl ")
        sql.AppendFormat("  where 1=1 ")
        sql.AppendFormat("  And YYMM='{0}' ", Me.ddlVersion.SelectedValue.Replace("TCReport", ""))
        sql.AppendFormat("  And HeaderTB='{0}' ", Me.HeaderTB)
        sql.AppendFormat("  And Locked='{0}' ", "Y")
        sql.AppendFormat(") ")

        'MyObj.WriteLog(sql.ToString())
        AuthDT = dat.GetData(sql.ToString())

        '上傳暫存檔
        Dim clsRandom As New clsRandom()
        Dim newName As String = Server.MapPath("~/") & "/tmp/" & clsRandom.Generate()
        FileUpload1.PostedFile.SaveAs(newName)

        Dim ExcelImportCheck As New ExcelImportCheck()
        Dim dt As New Data.DataTable

        '檢查工作表名稱
        Try
            Dim SheetName As String = ""
            If Me.ddlDataType.SelectedValue = "Percentage" Then
                SheetName = "Upload(Percentage)"
            Else
                SheetName = "Upload(Value)"
            End If
            If ExcelImportCheck.LoadData(dt, newName, SheetName, IO.Path.GetExtension(FileUpload1.PostedFile.FileName).ToUpper()) = False Then
                Return
            End If
        Catch ex As Exception
            MyObj.ShowMessage("Please check the file format!!!")
            Exit Sub
        End Try


        '重新命名欄位名稱及清空值
        Dim MonthAdd As Integer = 0
        Dim MonthCount As Integer
        Dim ErrorCount As Integer = 0
        Dim FilterValue As String = ""
        '存月份欄位字串
        Dim MonthString As String = ""
        Dim MonthScope As Integer = 0
        Dim TempDate As Date
        Dim GetMonth As String = ""

        Dim BaseColumns As Integer = 4 '非月份欄位有4欄
        '如果月份欄位名稱填入非日期或是為F開頭的欄位,則直接刪掉該欄位
        For i As Integer = dt.Columns.Count - 1 To BaseColumns Step -1
            If Not IsDate(dt.Columns(i).ColumnName) Then
                If Left(dt.Columns(i).ColumnName.ToString(), 1) = "F" Then
                    dt.Columns.RemoveAt(i)
                Else
                    MyObj.ShowMessage("Column [{0}] is out of scope.", dt.Columns(i).ColumnName)
                    Return
                End If
            End If
        Next

        If IsNumeric(TableName.Replace(Me.HeaderTB & "TCReport", "")) Then
            'MonthCount = DateTime.Now.Month
            MonthCount = Right(TableName, 2)
        Else
            If TableName.IndexOf("B") <> -1 Then
                'Budget
                MonthCount = 1
            Else
                'Trend
                MonthCount = 1
                'Dim tcManager As New TCReportReferManager(Me.ddlSignature.SelectedValue, TableName)
                'MonthCount = Right(tcManager.GetCurrent(), 2)
            End If

            MonthCount = IIf(MonthCount = 0, 1, MonthCount)
        End If

        MonthScope += 24 - MonthCount + 1   '計算當前月份到明年12月的範圍
        MonthScope += BaseColumns '加上基本欄位數量
        TempDate = New Date(Left(TableName.Replace(Me.HeaderTB & "TCReport", ""), 4), MonthCount, 1)

        If dt.Columns.Count >= (BaseColumns + 1) AndAlso dt.Columns.Count <= MonthScope Then
            For i As Integer = 0 To dt.Columns.Count - 1
                Select Case i
                    Case 0
                        dt.Columns(i).ColumnName = "ItemName"
                    Case 1
                        dt.Columns(i).ColumnName = "Signature"
                    Case 2
                        dt.Columns(i).ColumnName = "SecondLevel"
                    Case 3
                        dt.Columns(i).ColumnName = "ChannelID"
                    Case Else
                        If IsDate(dt.Columns(i).ColumnName) Then
                            GetMonth = dt.Columns(i).ColumnName.Split("/")(1)
                            Dim YearGap As Integer = dt.Columns(i).ColumnName.Split("/")(0) - Left(TableName.Replace(Me.HeaderTB & "TCReport", ""), 4)
                            If YearGap < 0 OrElse (Int32.Parse(GetMonth) + YearGap * 12) > 24 Then
                                ErrorCount += 1
                                MyObj.ShowMessage("{0} is out of scope.", dt.Columns(i).ColumnName)
                                Return
                            End If
                            dt.Columns(i).ColumnName = "Mon" & (Int32.Parse(GetMonth) + YearGap * 12)
                            MonthString &= dt.Columns(i).ColumnName & ","
                        End If
                End Select
                If i <> 0 Then FilterValue &= "or "
                FilterValue &= "convert(" & dt.Columns(i).ColumnName.ToString() & ",System.String)<>'' "
            Next
            '新增Error欄位
            dt.Columns.Add("Error", System.Type.GetType("System.String"))

            Dim MyDefaultView As Data.DataView = dt.DefaultView
            MyDefaultView.RowFilter = FilterValue
            dt = MyDefaultView.ToTable()
        ElseIf dt.Columns.Count < (BaseColumns + 1) Then
            ErrorCount += 1
            MyObj.ShowMessage(String.Format("Your column count should greater than {0}.", BaseColumns + 1))
            Return
        Else
            ErrorCount += 1
            MyObj.ShowMessage("Your column count should smaller than " & MonthScope & ".")
            Return
        End If
        '將欄位字串最後一個多的逗號去掉
        MonthString = MonthString.Substring(0, MonthString.Length - 1)


        '開始檢查資料
        Dim CheckRow As DataRow()
        Dim ItemName, Signature, SecondLevel, ChannelID As String
        Dim MatchtString As String = ""
        '暫存匯入的ItemName+Signature+SecondLevel+ChannelID
        Dim MatchDT As New Data.DataTable
        MatchDT.Columns.Add("MatchColumn", System.Type.GetType("System.String"))
        MatchDT.PrimaryKey = New DataColumn() {MatchDT.Columns(0)}
        Dim NewRow As DataRow
        For k As Integer = 0 To dt.Rows.Count - 1
            ItemName = dt.Rows(k)("ItemName").ToString().Trim()
            Signature = dt.Rows(k)("Signature").ToString().Trim()
            SecondLevel = dt.Rows(k)("SecondLevel").ToString().Trim()
            ChannelID = dt.Rows(k)("ChannelID").ToString().Trim()
            MatchtString = ItemName & Signature & SecondLevel & ChannelID

            If MatchDT.Rows.Contains(MatchtString) Then
                '比對項目是否重複
                dt.Rows(k)("Error") = "This Row is repetition."
            Else
                NewRow = MatchDT.NewRow()
                NewRow("MatchColumn") = MatchtString
                MatchDT.Rows.Add(NewRow)

                '比對ItemName
                If MyObj.CheckItemName(ItemName) = False Then
                    dt.Rows(k)("Error") = "The Grade is out of your scope."
                Else
                    '比對Signature
                    CheckRow = AuthDT.Select(String.Format("Signature='{0}'", Signature))
                    If CheckRow.Length = 0 Then
                        dt.Rows(k)("Error") = "The Signature is out of your scope."
                    Else
                        '比對SecondLevel
                        CheckRow = AuthDT.Select(String.Format("Signature='{0}' and SecondLevel='{1}'", Signature, SecondLevel))
                        If CheckRow.Length = 0 Then
                            dt.Rows(k)("Error") = String.Format("The {0} is out of your scope.", LevelName)
                        Else
                            '比對CustomerGroup
                            CheckRow = AuthDT.Select(String.Format("Signature='{0}' and ChannelID='{1}'", Signature, ChannelID))
                            If CheckRow.Length = 0 Then
                                dt.Rows(k)("Error") = String.Format("The CustomerGroup is out of your scope.", LevelName)
                            Else
                                '每個月份檢查
                                For Each mon As String In MonthString.Split(",")
                                    If dt.Rows(k)(mon).ToString() <> "" Then
                                        '不可為非數字　
                                        If IsNumeric(dt.Rows(k)(mon).ToString()) = False Then
                                            dt.Rows(k)("Error") = "Your column should be numeric."
                                            Exit For
                                        End If
                                    End If
                                Next
                            End If
                        End If
                    End If
                End If
            End If

            If dt.Rows(k)("Error").ToString() <> "" Then
                ErrorCount += 1
            End If
        Next


        '有異常則不寫入資料
        If (ErrorCount > 0) Then
            MyObj.ShowMessage("Upload Failed.Data Error Count:" & ErrorCount)
        Else
            If dt.Rows.Count > 0 Then
                Dim MyRandom As New clsRandom
                Dim RandomCode As String = MyRandom.Generate(8)

                '新增欄位
                Dim Mydc As New DataColumn("RandomCode", System.Type.GetType("System.String"))
                Mydc.DefaultValue = RandomCode
                dt.Columns.Add(Mydc)

                Using cn As New SqlConnection(ConfigurationManager.ConnectionStrings("connStr").ConnectionString)
                    cn.Open()
                    Using copy As New SqlBulkCopy(cn)
                        For i As Integer = 0 To dt.Columns.Count - 1
                            copy.ColumnMappings.Add(dt.Columns(i).ColumnName, dt.Columns(i).ColumnName)
                            'MyObj.WriteLog(dt.Columns(i).ColumnName)
                        Next
                        copy.DestinationTableName = "TCReportUByPHCG"
                        copy.WriteToServer(dt)
                    End Using
                End Using
                dt.Columns.Remove("RandomCode")

                Try
                    sql.Length = 0
                    'sql.AppendFormat("Update TCReportUByPHCG ")
                    'sql.AppendFormat("Set Signature=LTrim(RTrim(Signature)) ")
                    'sql.AppendFormat(",SecondLevel=LTrim(RTrim(SecondLevel)) ")
                    'sql.AppendFormat(",ChannelID=LTrim(RTrim(ChannelID)) ")
                    'sql.AppendFormat(",ItemName=LTrim(RTrim(ItemName)) ")
                    'sql.AppendFormat("where RandomCode='{0}' ", RandomCode)
                    If Me.ddlDataType.SelectedValue = "Percentage" Then
                        Dim YYMM As String = TableName.Replace(Me.HeaderTB & "TCReport", "")
                        'Update
                        sql.AppendFormat("Update @TB ")
                        sql.AppendFormat("set @TB.ItemName=S.ItemName ")
                        For i As Integer = MonthCount To 24
                            sql.AppendFormat(",@TB.PMon{0}=Case when S.Mon{0} is null then @TB.PMon{0} else S.Mon{0} end ", i.ToString())
                        Next
                        sql.AppendFormat(",UpdateBy='{0}' ", clsAuth.GetUserID())
                        sql.AppendFormat(",UpdateDate=GetDate() ")
                        sql.AppendFormat("from ")
                        sql.AppendFormat("( ")
                        sql.AppendFormat("  select * from TCReportUByPHCG where RandomCode='{0}' ", RandomCode)
                        sql.AppendFormat(")S ")
                        sql.AppendFormat("where 1=1 ")
                        sql.AppendFormat("and @TB.YYMM='{0}' ", YYMM)
                        sql.AppendFormat("and @TB.Signature=S.Signature ")
                        sql.AppendFormat("and @TB.LevelValue=S.SecondLevel ")
                        sql.AppendFormat("and @TB.Channel=S.ChannelID ")
                        sql.AppendFormat("and @TB.ItemName=S.ItemName ")

                        'Insert
                        sql.AppendFormat("Insert into @TB ")
                        sql.AppendFormat("Select '{0}' ", YYMM)
                        sql.AppendFormat(",S.Signature,'{0}',S.SecondLevel,S.ChannelID,S.ItemName ", LevelName)
                        For i As Integer = 1 To 24
                            sql.AppendFormat(",S.Mon{0} ", i.ToString())
                        Next
                        sql.AppendFormat(",'{0}' ", clsAuth.GetUserID())
                        sql.AppendFormat(",GetDate() ")
                        sql.AppendFormat(",null ")
                        sql.AppendFormat(",null ")
                        sql.AppendFormat("from TCReportUByPHCG S ")
                        sql.AppendFormat("where Not Exists ")
                        sql.AppendFormat("( ")
                        sql.AppendFormat("  Select top 1 * from @TB ")
                        sql.AppendFormat("  where 1=1 ")
                        sql.AppendFormat("  and @TB.YYMM='{0}' ", YYMM)
                        sql.AppendFormat("  and @TB.Signature=S.Signature ")
                        sql.AppendFormat("  and @TB.LevelValue=S.SecondLevel ")
                        sql.AppendFormat("  and @TB.Channel=S.ChannelID ")
                        sql.AppendFormat("  and @TB.ItemName=S.ItemName ")
                        sql.AppendFormat(") ")
                        sql.AppendFormat("and S.RandomCode='{0}' ", RandomCode)

                        sql.Replace("@TB", "TCReportFixedPercentage")
                    Else
                        'Update
                        sql.AppendFormat("Update @TB ")
                        sql.AppendFormat("set @TB.ItemName=S.ItemName ")
                        For i As Integer = MonthCount To 24
                            sql.AppendFormat(",@TB.Mon{0}=Case when S.Mon{0} is null then @TB.Mon{0} else S.Mon{0} end ", i.ToString())
                        Next
                        sql.AppendFormat("from ")
                        sql.AppendFormat("( ")
                        sql.AppendFormat("  select * from TCReportUByPHCG where RandomCode='{0}' ", RandomCode)
                        sql.AppendFormat(")S ")
                        sql.AppendFormat("where 1=1 ")
                        sql.AppendFormat("and @TB.Channel=S.ChannelID ")
                        sql.AppendFormat("and @TB.ItemName=S.ItemName ")
                        Dim HierString As String = ""
                        If LevelRow("Chain") = "Y" Then
                            HierString = String.Format("S.SecondLevel+REPLICATE('X',18-Len(S.SecondLevel)) ")
                        Else
                            HierString = String.Format("S.Signature+REPLICATE('X',2*({0}-2))+S.SecondLevel+REPLICATE('X',2*(9-{0})) ", LevelRow("Sequence"))
                        End If
                        sql.AppendFormat("and @TB.Hierarchy={0} ", HierString)

                        'Insert
                        Dim maxidmanager As New MaxIDManager("#" + TableName)
                        Dim ReportID As String = maxidmanager.GetCurrent()
                        sql.AppendFormat("Insert into @TB ")
                        sql.AppendFormat("Select '{0}' ", ReportID)
                        sql.AppendFormat(",S.ChannelID ")
                        sql.AppendFormat(",{0} ", HierString)
                        sql.AppendFormat(",S.ItemName ")
                        For i As Integer = 1 To 24
                            sql.AppendFormat(",S.Mon{0} ", i.ToString())
                        Next
                        sql.AppendFormat("from TCReportUByPHCG S ")
                        sql.AppendFormat("where Not Exists ")
                        sql.AppendFormat("( ")
                        sql.AppendFormat("  Select top 1 * from @TB ")
                        sql.AppendFormat("  where 1=1 ")
                        sql.AppendFormat("  and @TB.Channel=S.ChannelID ")
                        sql.AppendFormat("  and @TB.ItemName=S.ItemName ")
                        sql.AppendFormat("  and @TB.Hierarchy={0} ", HierString)
                        sql.AppendFormat(") ")
                        sql.AppendFormat("and S.RandomCode='{0}' ", RandomCode)
                        sql.Replace("@TB", TableName)
                    End If

                    sql.AppendFormat("Delete from TCReportUByPHCG where RandomCode='{0}' ", RandomCode)
                    'MyObj.WriteLog(sql.ToString())
                    dat.AccData(sql.ToString())

                    MyObj.ShowMessage("Upload Successful.Data Success Count:" & dt.Rows.Count)
                Catch ex As Exception
                    MyObj.WriteLog(ex.ToString())
                    MyObj.ShowMessage("Upload Failed.")
                End Try
            Else
                MyObj.ShowMessage("Upload Failed.")
            End If
        End If


        '改顯示欄位名稱
        Dim CountAdd As Integer = 0
        Dim ErrorFilterValue As String = ""
        For a As Integer = 0 To dt.Columns.Count - 1
            If a = 0 Then
                dt.Columns(a).ColumnName = "Grade"
            ElseIf a = 1 Then
                dt.Columns(a).ColumnName = "Signature"
            ElseIf a = 2 Then
                dt.Columns(a).ColumnName = "Brand or Axe"
            ElseIf a = 3 Then
                dt.Columns(a).ColumnName = "Customer Group"
            ElseIf a >= 1 And a <> dt.Columns.Count - 1 Then

                If CountAdd = 0 Then
                    If IsDate(dt.Columns(a).ColumnName) Then
                        dt.Columns(a).ColumnName = TempDate.ToString("yyyy/MM")
                    End If
                    CountAdd = 1
                Else
                    If IsDate(dt.Columns(a).ColumnName) Then
                        dt.Columns(a).ColumnName = TempDate.AddMonths(CountAdd).ToString("yyyy/MM")
                    End If
                    CountAdd += 1
                End If

            ElseIf a = dt.Columns.Count - 1 Then

                dt.Columns(a).ColumnName = "Error"
                dt.Columns(a).SetOrdinal(0)

            End If

        Next

        dt.DefaultView.RowFilter = "convert(Error,System.String)<>''"
        TCGVDT = dt.DefaultView.ToTable

        Me.BindGV()
    End Sub

    Protected Sub gv_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles gv.PageIndexChanging
        Me.gv.PageIndex = e.NewPageIndex
        Me.BindGV()
    End Sub
#End Region

#Region "繫結資料"
    Protected Sub BindDDLUploadType()
        ddlUploadType.DataTextField = "Text"
        ddlUploadType.DataValueField = "Value"
        ddlUploadType.DataSource = MyObj.GetCodeMappingData("ForecastUpload", "UploadType")
        ddlUploadType.DataBind()

        If ddlUploadType.Items.FindByValue("PLV") IsNot Nothing Then
            ddlUploadType.Items.Remove(ddlUploadType.Items.FindByValue("PLV"))
        End If
    End Sub

    Protected Sub BindDDLDataType()
        Dim ddl As DropDownList = Me.ddlDataType
        ddl.DataTextField = "Text"
        ddl.DataValueField = "Value"
        ddl.DataSource = MyObj.GetCodeMappingData("ForecastUpload", "DataType")
        ddl.DataBind()
    End Sub
    Protected Sub BindVersion()
        Me.ddlVersion.Items.Clear()
        Me.ddlVersion.Items.Add(New ListItem(DateTime.Now.ToString("yyyy/MM"), "TCReport" & DateTime.Now.ToString("yyyyMM")))
        Me.ddlVersion.Items.Add(New ListItem(DateTime.Now.AddMonths(1).ToString("yyyy/MM"), "TCReport" & DateTime.Now.AddMonths(1).ToString("yyyyMM")))
        Me.ddlVersion.Items.Add(New ListItem(DateTime.Now.ToString("yyyy") & "Budget", "TCReport" & DateTime.Now.ToString("yyyy") & "B"))
        Me.ddlVersion.Items.Add(New ListItem(DateTime.Now.ToString("yyyy") & "Trend", "TCReport" & DateTime.Now.ToString("yyyy") & "T"))
        Me.ddlVersion.Items.Add(New ListItem(DateTime.Now.AddYears(1).ToString("yyyy") & "Budget", "TCReport" & DateTime.Now.AddYears(1).ToString("yyyy") & "B"))
        Me.ddlVersion.SelectedIndex = -1
    End Sub
    Private Sub BindDDLDivision()
        Dim ddlControl As DropDownList = Me.ddlDivision
        Dim dvManager As New DivisionManager

        ddlControl.DataTextField = "Division"
        ddlControl.DataValueField = "Division"
        ddlControl.DataSource = dvManager.GetAllDivision()
        ddlControl.DataBind()
    End Sub

    Protected Sub BindGV()
        gv.DataSource = TCGVDT
        gv.DataBind()
        If gv.Rows.Count > 0 Then
            gv.Visible = True
        Else
            gv.Visible = False
        End If
    End Sub
#End Region

#Region "屬性"
    Public Property TCGVDT() As Data.DataTable
        Get
            Return ViewState("TCGVDT")
        End Get
        Set(ByVal value As Data.DataTable)
            ViewState("TCGVDT") = value
        End Set
    End Property

    Public ReadOnly Property HeaderTB() As String
        Get
            Select Case Me.ddlUploadType.SelectedValue
                Case "SellOut"
                    Return "SO_"
                Case "PLV"
                    Return "PLV_"
                Case Else
                    Return ""
            End Select
        End Get
    End Property
#End Region
End Class
